/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package models.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;
import models.beans.User;
import models.DBHandler;
import models.beans.UserBean;
/**
 *
 * @author Group 3 Aprotrain Aptech Group3 Aprotrain Aptech
 */
public class UserDAO {
    public static User loginUser(String userId,String password) throws Exception
    {
        User userTmp = null;
        Connection conn = DBHandler.getConnection();
        ResultSet rs = null;
        PreparedStatement pstm = null;
        try {
            pstm = conn.prepareStatement("SELECT * FROM tblUser WHERE userId = ? AND password = ?");
            pstm.setString(1, userId);
            pstm.setString(2, password);
            rs = pstm.executeQuery();
            if(rs.next()){
               userTmp = new User();
               userTmp.setUserId(rs.getString("userId"));
            userTmp.setUserName(rs.getString("userName"));
            userTmp.setPassword(rs.getString("password"));
            userTmp.setSex(rs.getString("sex"));
            userTmp.setIsFirstLogin(rs.getInt("isFirstLogin"));
            userTmp.setAddress(rs.getString("address"));
            userTmp.setBirthday(convertDateToString(rs.getDate("birthday")));
            userTmp.setStatus(rs.getInt("status"));
            if(rs.getInt("status") == 0){
                userTmp.setStatusString("Offline");
            }
            else{
                userTmp.setStatusString("Online");
            }
            userTmp.setIsStaff(rs.getInt("isStaff"));
            userTmp.setClassUser(rs.getString("class"));
            userTmp.setProfessional(rs.getString("professional"));
            userTmp.setPersonalDetails(rs.getString("personalDetails"));
            }
            } catch (Exception ex) {
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return userTmp;
    }
    public static List<UserBean> getAllUsersByCondition() throws ClassNotFoundException, SQLException {
        List<UserBean> list = new ArrayList<UserBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblUser");
        rs = pstm.executeQuery();

        UserBean userTmp = null;
        while (rs.next()) {
            userTmp = new UserBean();
            userTmp.setUserId(rs.getString("userId"));
            userTmp.setUserName(rs.getString("userName"));
            userTmp.setPassword(rs.getString("password"));
            userTmp.setSex(rs.getString("sex"));
            userTmp.setIsFirstLogin(rs.getInt("isFirstLogin"));
            userTmp.setAddress(rs.getString("address"));
            userTmp.setClassUser(rs.getString("class"));
            userTmp.setBirthday(convertDateToString(rs.getDate("birthday")));
            userTmp.setStatus(rs.getInt("status"));
            userTmp.setIsStaff(rs.getInt("isStaff"));
            userTmp.setClassUser(rs.getString("class"));
            userTmp.setProfessional(rs.getString("professional"));
            userTmp.setPersonalDetails(rs.getString("personalDetails"));

            list.add(userTmp);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static List<UserBean> getAllStudent() throws ClassNotFoundException, SQLException {
        List<UserBean> list = new ArrayList<UserBean>();
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblUser WHERE isStaff = 0");
        rs = pstm.executeQuery();

        UserBean userTmp = null;
        while (rs.next()) {
            userTmp = new UserBean();
            userTmp.setUserId(rs.getString("userId"));
            userTmp.setUserName(rs.getString("userName"));
            userTmp.setPassword(rs.getString("password"));
            userTmp.setSex(rs.getString("sex"));
            userTmp.setClassUser(rs.getString("class"));
            userTmp.setIsFirstLogin(rs.getInt("isFirstLogin"));
            userTmp.setAddress(rs.getString("address"));
            userTmp.setBirthday(convertDateToString(rs.getDate("birthday")));
            userTmp.setStatus(rs.getInt("status"));
            if(rs.getInt("status") == 0){
                userTmp.setStatusString("Offline");
            }
            else{
                userTmp.setStatusString("Online");
            }
            userTmp.setIsStaff(rs.getInt("isStaff"));
            userTmp.setClassUser(rs.getString("class"));
            userTmp.setProfessional(rs.getString("professional"));
            userTmp.setPersonalDetails(rs.getString("personalDetails"));

            list.add(userTmp);
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return list;
    }
    public static UserBean getUserDetais(String userId) throws ClassNotFoundException, SQLException {
       
        ResultSet rs = null;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("SELECT * FROM tblUser WHERE userId = ?");
        pstm.setString(1, userId);
        rs = pstm.executeQuery();

        UserBean userTmp = null;
        if (rs.next()) {
            userTmp = new UserBean();
            userTmp.setUserId(rs.getString("userId"));
            userTmp.setUserName(rs.getString("userName"));
            userTmp.setPassword(rs.getString("password"));
            userTmp.setSex(rs.getString("sex"));
            userTmp.setIsFirstLogin(rs.getInt("isFirstLogin"));
            userTmp.setAddress(rs.getString("address"));
            userTmp.setBirthday(convertDateToString(rs.getDate("birthday")));
            userTmp.setStatus(rs.getInt("status"));
            if(rs.getInt("status") == 0){
                userTmp.setStatusString("Offline");
            }
            else{
                userTmp.setStatusString("Online");
            }
            userTmp.setIsStaff(rs.getInt("isStaff"));
            userTmp.setClassUser(rs.getString("class"));
            userTmp.setProfessional(rs.getString("professional"));
            userTmp.setPersonalDetails(rs.getString("personalDetails"));
            
        }
        DBHandler.closeDBObj(rs);
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);

        return userTmp;
    }
    public static int addNewUser(UserBean newuser) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("INSERT INTO [Mail_Server].[dbo].[tblUser]([userId],[userName] ,[password] ,[sex] ,[address] ,[birthday] ,[status] ,[isStaff],[class],[professional],[personalDetails],[isFirstLogin]) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
        pstm.setString(1, newuser.getUserId());
        pstm.setString(2, newuser.getUserName());
        pstm.setString(3, "a123123");
        pstm.setString(4, newuser.getSex());        
        pstm.setString(5, newuser.getAddress());
        pstm.setDate(6, getInsertDate(newuser.getBirthday()));
        pstm.setInt(7, newuser.getStatus());
        pstm.setInt(8, newuser.getIsStaff());
        pstm.setString(9, newuser.getClassUser());
        pstm.setString(10, newuser.getProfessional());
        pstm.setString(11, newuser.getPersonalDetails());
        pstm.setInt(12, newuser.getIsFirstLogin());

        result = pstm.executeUpdate();

        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }

    public static int updateUser(UserBean newuser) throws ClassNotFoundException, SQLException {
        int result = 0;

        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("UPDATE [Mail_Server].[dbo].[tblUser] SET [userName] = ? ,[password] = ?,[sex] = ?,[isFirstLogin] = ?,[address] =?,[birthday] =?,[status] =?,[isStaff] =?,[class] =?,[professional] =?,[personalDetails] =? WHERE [userId] = ?");
        pstm.setString(1, newuser.getUserName());
        pstm.setString(2, newuser.getPassword());
        pstm.setString(3, newuser.getSex());
        pstm.setInt(4, newuser.getIsFirstLogin());
        pstm.setString(5, newuser.getAddress());
        pstm.setDate(6, getInsertDate(newuser.getBirthday()));
        pstm.setInt(7, newuser.getStatus());
        pstm.setInt(8, newuser.getIsStaff());
        pstm.setString(9, newuser.getClassUser());
        pstm.setString(10, newuser.getProfessional());
        pstm.setString(11, newuser.getPersonalDetails());
        pstm.setString(12, newuser.getUserId());

        result = pstm.executeUpdate();

        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }

    public static int updatePassword(UserBean newuser) throws ClassNotFoundException, SQLException {
        int result = 0;
        System.out.println(newuser.getUserId());
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("UPDATE [Mail_Server].[dbo].[tblUser] SET [password] = ?,[isFirstLogin]=?  WHERE [userId]=?");
        pstm.setString(1, newuser.getPassword());
        pstm.setInt(2, 1);
        pstm.setString(3, newuser.getUserId());

        result = pstm.executeUpdate();
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static int updateStatus(int status,String userId) throws ClassNotFoundException, SQLException {
        int result = 0;       
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("UPDATE [Mail_Server].[dbo].[tblUser] SET [status] = ?  WHERE [userId]=?");
        pstm.setInt(1, status);
        pstm.setString(2, userId);

        result = pstm.executeUpdate();
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }

    public static int deleteUser(String userId) throws ClassNotFoundException, SQLException {
        int result = 0;
        Connection conn = DBHandler.getConnection();
        PreparedStatement pstm = conn.prepareStatement("DELETE FROM [tblUser] WHERE userId=?");
        pstm.setString(1, userId);
        result = pstm.executeUpdate();
        DBHandler.closeDBObj(pstm);
        DBHandler.closeDBObj(conn);
        return result;
    }
    public static Date getInsertDate(String s){
        java.sql.Date dte=null;
        try{
        String str = s;
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        java.util.Date dt = formatter.parse(str);
        dte=new java.sql.Date(dt.getTime());
        }catch(Exception e){
        e.printStackTrace();
        }
        return dte;
    }
    public static String convertDateToString(java.util.Date date) {
        SimpleDateFormat mySimpleDateFormat = new SimpleDateFormat("dd/MM/yyyy");
        return mySimpleDateFormat.format(date);
    }
}
